package MemberController;

import java.sql.*;





import java.util.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;



import Connect.connector;

public class MembersDAO{
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public String mCheckName(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql = "select Mem_NameK from MEMBERS where Mem_Nick=?";
			
			System.out.println("*****아이디체크시작*****");
			System.out.println(mbean.getMem_Nick());
		
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1,mbean.getMem_Nick());
		
			
			rs = pstmt.executeQuery();
			
			if( rs.next() ) {
				
				String name = rs.getString(1);
				return name;
			}
			
		}catch( Exception e ){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	return null;
}
	
	
	public boolean mCheckId(MembersBean mbean){
		conn = connector.getConnection();
		try{
			String sql = "select Mem_Nick from MEMBERS where Mem_Nick=?";
			
			System.out.println("*****아이디체크시작*****");
			System.out.println(mbean.getMem_Nick());
		
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1,mbean.getMem_Nick());
		
			
			rs = pstmt.executeQuery();
			
			while ( rs.next() ) {
				
				if(mbean.getMem_Nick().equals(rs.getString("Mem_Nick"))){
				System.out.println("*****아이디 체크 성공*****");	
				return true;
				}
			}
		}catch( Exception e ){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	return false;
}
	
	public boolean mCheckPass(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql = "select Mem_Pass  from MEMBERS where Mem_Pass=? and Mem_Nick=?";
			
			System.out.println("*****비밀번호체크시작*****");
			
			System.out.println(mbean.getMem_Pass());
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1,mbean.getMem_Pass());
			pstmt.setString(2,mbean.getMem_Nick());
			rs = pstmt.executeQuery();
			
			while ( rs.next() ) {
				
				if(mbean.getMem_Pass().equals(rs.getString("Mem_Pass"))){
					System.out.println("*****비밀번호 체크 성공*****");	
					return true;
				}
				
			}
		}catch( Exception e ){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	return false;
	}
	
	
	public void mjoin(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql = "insert into MEMBERS(ID,Mem_Nick,Mem_Pass,Mem_Question,Mem_Answer,Mem_Fpost,Mem_Spost,Mem_Address1,Mem_Address2,Mem_Phone,Mem_Mobile,Mem_Email,Mem_Fax,Mem_NameK,Mem_NameH,Mem_Level,Mem_Year,Mem_Month,Mem_Day,Mem_Date)"  
	            + " values(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,Sysdate()) ";
			 	
			 	
			 pstmt = conn.prepareStatement( sql );
			    
			 pstmt.setString(1,mbean.getMem_Nick());
			 pstmt.setString(2,mbean.getMem_Pass());
			 pstmt.setString(3,mbean.getMem_Question());
			 pstmt.setString(4,mbean.getMem_Answer());
			 pstmt.setString(5,mbean.getMem_Fpost());
			 pstmt.setString(6,mbean.getMem_Spost());
			 pstmt.setString(7,mbean.getMem_Address1());
			 pstmt.setString(8,mbean.getMem_Address2());
			 pstmt.setString(9,mbean.getMem_Phone());
			 pstmt.setString(10,mbean.getMem_Mobile());
			 pstmt.setString(11,mbean.getMem_Email());
			 pstmt.setString(12,mbean.getMem_Fax());
			 pstmt.setString(13,mbean.getMem_NameK());
			 pstmt.setString(14,mbean.getMem_NameH());
			 pstmt.setInt(15,mbean.getMem_Level());
			 pstmt.setString(16,mbean.getMem_Year());
			 pstmt.setString(17,mbean.getMem_Month());
			 pstmt.setString(18,mbean.getMem_Day());
			  
			pstmt.executeUpdate();
				
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		
	}
	public int mCheckLevel(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql = "select Mem_Level from MEMBERS where Mem_Nick=?";
			
			System.out.println("*****레벨구별시작*****");
			System.out.println(mbean.getMem_Nick());
		
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1,mbean.getMem_Nick());
		
			
			rs = pstmt.executeQuery();
			
			while ( rs.next() ) {
				
				if(rs.getInt("Mem_Level")==1){
				System.out.println("*****할배/할매 회원*****");	
				return 1;
				}else if(rs.getInt("Mem_Level")==2){
				System.out.println("*****피도안마른애들*****");	
				return 2;
				}else if(rs.getInt("Mem_Level")==3){
				System.out.println("*****어이쿠 관리자님*****");		
				return 3;
				}
			}
		}catch( Exception e ){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	return -1;
}
	
	public String findId(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql = "select Mem_Nick from MEMBERS where Mem_NameK=? and Mem_Email=? and Mem_Year=? and Mem_Month=? and Mem_Day=?";
			
			System.out.println("*****아이디찾기시작*****");
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1,mbean.getMem_NameK());
			pstmt.setString(2,mbean.getMem_Email());
			pstmt.setString(3,mbean.getMem_Year());
			pstmt.setString(4,mbean.getMem_Month());
			pstmt.setString(5,mbean.getMem_Day());
		
			rs = pstmt.executeQuery();
			
			while ( rs.next() ) {
				
				String id = rs.getString("Mem_Nick");
				return id;
			}
		}catch( Exception e ){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	return null;
}
	public String findPass(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql = "select Mem_Pass from MEMBERS where Mem_Nick=? and Mem_Question=? and Mem_Answer=? and Mem_Email=?";
			
			System.out.println("*****패스워드 찾기시작*****");
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1,mbean.getMem_Nick());
			pstmt.setString(2,mbean.getMem_Question());
			pstmt.setString(3,mbean.getMem_Answer());
			pstmt.setString(4,mbean.getMem_Email());
			System.out.println(pstmt);
			rs = pstmt.executeQuery();
			
			while ( rs.next() ) {
				
				String pass = rs.getString("Mem_Pass");
				System.out.println(pass);
				return pass;
			}
			
		}catch( Exception e ){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	return null;
}
	
	
	public ArrayList memberList(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql="select * from MEMBERS where Mem_Nick=?";
		
			ArrayList memberList = new ArrayList();
		
			pstmt = conn.prepareStatement( sql );
	
			pstmt.setString(1,mbean.getMem_Nick());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				mbean.setMem_Nick(rs.getString("Mem_Nick"));
				mbean.setBlogs_Id(rs.getInt("Blogs_Id"));
				mbean.setMem_Address1(rs.getString("Mem_Address1"));
				mbean.setMem_Address2(rs.getString("Mem_Address2"));
				mbean.setMem_Email(rs.getString("Mem_Email"));
				mbean.setMem_Fax(rs.getString("Mem_Fax"));
				mbean.setMem_Fpost(rs.getString("Mem_FPost"));
				mbean.setMem_Spost(rs.getString("Mem_Spost"));
				mbean.setMem_Level(rs.getInt("Mem_Level"));
				mbean.setMem_Phone(rs.getString("Mem_Phone"));
				mbean.setMem_Mobile(rs.getString("Mem_Mobile"));
				mbean.setMem_Year(rs.getString("Mem_Year"));
				mbean.setMem_Month(rs.getString("Mem_Month"));
				mbean.setMem_Day(rs.getString("Mem_Day"));
				mbean.setMem_Date(rs.getDate("Mem_Date"));
				mbean.setMem_NameK(rs.getString("Mem_NameK"));
				mbean.setMem_Pass(rs.getString("Mem_Pass"));
				
				memberList.add(mbean);
				
			}
		return memberList;
		
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	
		return null;
	}
		
public void mUpdate(MembersBean mbean){
		conn = connector.getConnection();
		try{
			
			String sql = "update MEMBERS set Mem_Fpost=?,Mem_Spost=?,Mem_Address1=?,Mem_Address2=?,Mem_Phone=?,Mem_Mobile=?,Mem_Email=?,Mem_Fax=? where Mem_Nick=?";
			 	
			 
			pstmt = conn.prepareStatement( sql );
			    
			 pstmt.setString(1,mbean.getMem_Fpost());
			 pstmt.setString(2,mbean.getMem_Spost());
			 pstmt.setString(3,mbean.getMem_Address1());
			 pstmt.setString(4,mbean.getMem_Address2());
			 pstmt.setString(5,mbean.getMem_Phone());
			 pstmt.setString(6,mbean.getMem_Mobile());
			 pstmt.setString(7,mbean.getMem_Email());
			 pstmt.setString(8,mbean.getMem_Fax());
			 pstmt.setString(9,mbean.getMem_Nick()); 
			 pstmt.executeUpdate();
				
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		
	}

public boolean RePass(MembersBean mbean){
	conn = connector.getConnection();
	
	try{
		String sql = "select Mem_Pass  from MEMBERS where Mem_Nick=?";
		
		System.out.println("*****비밀번호체크시작*****");
		
		pstmt = conn.prepareStatement( sql );
		
		pstmt.setString(1,mbean.getMem_Nick());
		
		rs = pstmt.executeQuery();
		
		while ( rs.next() ) {
			
			if(mbean.getMem_Pass().equals(rs.getString("Mem_Pass"))){
				System.out.println("*****비밀번호 체크 성공*****");	
				return true;
			}
		}
	}catch( Exception e ){
		e.printStackTrace();
	}finally{
		connector.releaseConnection(conn, pstmt, rs);
	}
return false;
}
public void RePassUpdate(MembersBean mbean){
	conn = connector.getConnection();
	try{
		String sql = "update MEMBERS set Mem_Pass=? where Mem_Nick=?";
		 	
		pstmt = conn.prepareStatement( sql );
		pstmt.setString(1,mbean.getMem_Pass());
		pstmt.setString(2,mbean.getMem_Nick());
		pstmt.executeUpdate();
		 
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		connector.releaseConnection(conn, pstmt, rs);
	}
	
}

 public boolean mdeleteC(MembersBean mbean){
	conn = connector.getConnection();
	try{
		
		String sql = "select Mem_Pass,Mem_Email from MEMBERS where Mem_Nick=?";
		
		pstmt = conn.prepareStatement(sql);
		
		pstmt.setString(1,mbean.getMem_Nick());
		
		rs = pstmt.executeQuery();
		
		while(rs.next()){
				if(mbean.getMem_Pass().equals(rs.getString("Mem_Pass")) &&  mbean.getMem_Email().equals(rs.getString("Mem_Email"))){
					return true;
				}
			}
		}catch(Exception e){
		   e.printStackTrace();
	   }finally{
		   connector.releaseConnection(conn, pstmt, rs);
		}
	return false;
}	
	


public void mdelete(MembersBean mbean){
	conn = connector.getConnection();
	
	try{
		    
		   String sql="delete from MEMBERS where Mem_Nick=?";
			
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1,mbean.getMem_Nick());
			
			System.out.println("*****인증성공!!*****");
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,mbean.getMem_Nick());
				pstmt.executeUpdate();
						
		}catch(Exception e){
		   e.printStackTrace();
	   }finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	  
}	

public String mCheckTown(String town){
	conn = connector.getConnection();

	try{
		
		String sql = "select town from zip_code where town like ?";
		
		pstmt = conn.prepareStatement(sql);
		
		pstmt.setString(1,"%"+town+"%");
		
		rs = pstmt.executeQuery();
		
		while(rs.next()){
				
				
					System.out.println("해당마을있음");
					return "1";
			}
		
		}catch(Exception e){
		   e.printStackTrace();
	   }finally{
		   connector.releaseConnection(conn, pstmt, rs);
		}
	   System.out.println("해당마을없음");
	   return "2";
}	


public ArrayList memZipcheck(String town){
	conn = connector.getConnection();
	try{
		
		System.out.println("이름:"+town);
		
		String sql = "select zipcode, pref , city , town  from zip_code where town like ?";
		
		
		ArrayList beanList = new ArrayList ();
		
		pstmt = conn.prepareStatement( sql );
		
		pstmt.setString(1,"%"+town+"%");
		System.out.println(pstmt);
		
		rs = pstmt.executeQuery();
		
		while(rs.next()){
			
			ZipcodeBean zbean = new ZipcodeBean();
			System.out.println(rs.getString("zipcode"));
			System.out.println(rs.getString("pref"));
			System.out.println(rs.getString("city"));
			System.out.println(rs.getString("town"));
			zbean.setZipcode(rs.getString("zipcode"));
			zbean.setPref(rs.getString("pref"));
			zbean.setCity(rs.getString("city"));
			zbean.setTown(rs.getString("town"));
			
			beanList.add(zbean);
		
			
		}
		return beanList;
		}catch(Exception e){
		e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return null;
}

}